# -*- coding:utf-8 -*-
import os
import uuid
from datetime import datetime

import redis
import torndb
import xlrd

import setting

class ImportOnceCardExcel(object):

    """
    商品通过Excel文件形式导入

    """

    def __init__(self):

        self.excel_filepath = u'/home/dev/zzh/import/'  # 文件存放目录
        self.oncecard_excel_file = self.excel_filepath + u'oncecard.xlsx'  # excel文件

        self.db = torndb.Connection(
            host=setting.mysql_host,
            database=setting.mysql_database,
            user=setting.mysql_user,
            password=setting.mysql_password,
            charset='utf8'
        )

        self.redis = redis.StrictRedis(host=setting.redis_host, port=setting.redis_port)

    def _get_excel_data(self, filename):
        """
        读取excel数据
            1）校验excel格式数据
            2）写入list

        :param file: 导入的excel文件
        :return:
        """
        try:
            data = xlrd.open_workbook(filename)
        except Exception, e:
            print str(e)
            return

        table = data.sheets()[0]
        nrows = table.nrows  # 行数
        ncols = table.ncols  # 列数
        if ncols < 1:
            print "no data import."
            return

        codes = []
        for rownum in range(0, nrows):
            row = table.row_values(rownum)
            if row:
                if len(row[0]) == 0:
                    continue

                app = {}

                app['qrcode'] = str(row[0]).strip()

                # 校验qrcode是否有效
                # 过滤空码
                if not app['qrcode']:
                    continue

                # 是否已经导入过
                if self._code_exist(app['qrcode']):
                    return None, app['qrcode']

                codes.append(app)

        return codes, None

    def _code_exist(self, code):
        return self.db.get("select * from t_qrcode_for_oncecard where code=%(code)s", code=code)


    def _check_product_name_exist(self, product_name):
        """
        防止重复导入

        :param product_name: code
        :return:
        """
        pass

    def _insert_code(self, code):
        """
        将code插入t_qrcode_for_oncecard表

        :param params:
        :return:
        """
        oncecard_id = str(uuid.uuid4()).replace("-", "")
        self.db.execute(
            "insert into t_qrcode_for_oncecard (id, code, create_date, update_date) "
            "values (%(id)s, %(code)s, now(), now())", id=oncecard_id, code=code)

    def runner(self):

        if not os.path.exists(self.oncecard_excel_file):
            print "Excel文件:{} 不存在!".format(self.oncecard_excel_file)
            return

        print "开始读取文件:{}".format(self.oncecard_excel_file)

        codes, code = self._get_excel_data(self.oncecard_excel_file)
        if code:
            print "次卡码:{}已经导入过，请删除后重新导入.".format(code)
            return

        if not codes:
            print "没有需要导入的次卡码数据."
            return

        for c in codes:
            print "正在导入次卡码:{}".format(c.get("qrcode"))
            self._insert_code(c.get("qrcode"))

if __name__ == '__main__':
    print "run start : {}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    ioce = ImportOnceCardExcel()
    ioce.runner()
    ioce.db.close()
    os.remove(ioce.oncecard_excel_file)
    print "run end : {}".format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"))